﻿using System;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Text;

namespace PPI.Mobile.General
{
    /// <summary>
    /// Various utilities.
    /// </summary>
    public static class MobileGeneral
    {
        #region DB
        /// <summary>
        /// Opens and returns a sql connection
        /// </summary>
        public static SqlCeConnection GetConnection(string connString)
        {
            //Created by: VMCoder
            //Creation date: 2008.04.21
            //Last updated by: VMCoder
            //Last update: 2008.04.21

            try
            {


                AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
                if (connString.IndexOf("Data Source") < 0)
                    connString = "Data Source = " + connString + ";";
                SqlCeConnection _Con = new SqlCeConnection(connString);
                if (_Con.State != ConnectionState.Open)
                    _Con.Open();

                return _Con;
            }
            catch
            {
                throw;
            }
        }
        /// <summary>
        /// Tests database against Pole Position Mobile Standards
        /// </summary>
        public static bool TestDatabase(string _ConnString)
        {
            //Created by: VMCoder
            //Creation date: 2008.04.21
            //Last updated by: VMCoder
            //Last update: 2008.04.21

            string _Sql;
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt;
            try
            {
                _Con = GetConnection(_ConnString);

                #region tb_Vendor_Visits
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    VisitId,";
                _Sql += "\n    AssetFk,";
                _Sql += "\n    VisitTypeFk,";
                _Sql += "\n    Latitude,";
                _Sql += "\n    Longitude,";
                _Sql += "\n    Altitude,";
                _Sql += "\n    SatelliteCount,";
                _Sql += "\n    SatelliteTime,";
                _Sql += "\n    SolutionAvailable,";
                _Sql += "\n    UtilityFk,";
                _Sql += "\n    ProjectFk,";
                _Sql += "\n    VendorFk,";
                _Sql += "\n    UserFk,";
                _Sql += "\n    Handheld,";
                _Sql += "\n    TopTypeFk";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Vendor_Visits";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                #endregion tb_Vendor_Visits

                #region tb_Vendor_Visit_XStructureMappingData
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    VisitFk,";
                _Sql += "\n    DataId,";
                _Sql += "\n    AID";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Vendor_Visit_XStructureMappingData";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                #endregion tb_Vendor_Visit_XStructureMappingData

                #region tb_Vendor_Visit_XStructureMappingPoles
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    LVisitor,";
                _Sql += "\n    PoleId,";
                _Sql += "\n    DataFk,";
                _Sql += "\n    TopTypeFk,";
                _Sql += "\n    SpeciesFk,";
                _Sql += "\n    TreatmentFk,";
                _Sql += "\n    Year,";
                _Sql += "\n    ClassFk,";
                _Sql += "\n    Latitude,";
                _Sql += "\n    Longitude,";
                _Sql += "\n    Altitude,";
                _Sql += "\n    SatelliteCount,";
                _Sql += "\n    SatelliteTime,";
                _Sql += "\n    SolutionAvailable,";
                _Sql += "\n    AID,";
                _Sql += "\n    LVisit,";
                _Sql += "\n    Number,";
                _Sql += "\n    Length";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Vendor_Visit_XStructureMappingPoles";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                #endregion tb_Vendor_Visit_XStructureMappingPoles

                #region tb_Vendor_Visit_Images
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    PoleFk,";
                _Sql += "\n    Filename,";
                _Sql += "\n    SolutionAvailable,";
                _Sql += "\n    SatelliteTime,";
                _Sql += "\n    SatelliteCount,";
                _Sql += "\n    Altitude,";
                _Sql += "\n    Longitude,";
                _Sql += "\n    Latitude,";
                _Sql += "\n    Direction,";
                _Sql += "\n    VisitFk,";
                _Sql += "\n    ImageId";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Vendor_Visit_Images";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                #endregion tb_Vendor_Visit_Images

                #region tb_Tops
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    TopId,";
                _Sql += "\n    Name,";
                _Sql += "\n    VisitTypeFk";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Tops";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                #endregion tb_Tops

                #region tb_Lookups
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    LookupId,";
                _Sql += "\n    LookupTypeFk,";
                _Sql += "\n    LookupText,";
                _Sql += "\n    LookupValue";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Lookups";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                #endregion tb_Lookups

                #region tb_Settings
                #region Query
                _Sql = "";
                _Sql += "\nSELECT";
                _Sql += "\n    SettingId,";
                _Sql += "\n    SettingKey,";
                _Sql += "\n    SettingName,";
                _Sql += "\n    SettingValue";
                _Sql += "\nFROM";
                _Sql += "\n    tb_Settings";
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql, _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                #endregion tb_Settings

                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (_Con != null)
                    _Con.Close();
            }
        }
        /// <summary>
        /// Creates and returns a sql parameter
        /// </summary>
        public static SqlCeParameter CreateParameter(string parameterName, SqlDbType dbType, int size, ParameterDirection direction, bool isNullable, byte precision, byte scale, object value)
        {
            //Created by: -
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return new SqlCeParameter(parameterName, dbType, size, direction, isNullable, precision, scale, String.Empty, DataRowVersion.Default, value);
        }
        /// <summary>
        /// Creates and returns a sql Int parameter
        /// </summary>
        public static SqlCeParameter CreateIntParameter(string parameterName, ParameterDirection direction, bool isNullable, object value)
        {
            //Created by: -
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return CreateParameter(parameterName, SqlDbType.Int, 0, direction, isNullable, 0, 0, value);
        }
        /// <summary>
        /// Creates and returns a sql Bit parameter
        /// </summary>
        public static SqlCeParameter CreateBitParameter(string parameterName, ParameterDirection direction, bool isNullable, object value)
        {
            //Created by: -
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return CreateParameter(parameterName, SqlDbType.Bit, 0, direction, isNullable, 0, 0, value);
        }
        /// <summary>
        /// Creates and returns a sql DateTime parameter
        /// </summary>
        public static SqlCeParameter CreateDateTimeParameter(string parameterName, ParameterDirection direction, bool isNullable, object value)
        {
            //Created by: -
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return CreateParameter(parameterName, SqlDbType.DateTime, 0, direction, isNullable, 0, 0, value);
        }
        /// <summary>
        /// Creates and returns a sql NVarChar parameter
        /// </summary>
        public static SqlCeParameter CreateNVarCharParameter(string parameterName, int size, ParameterDirection direction, bool isNullable, object value)
        {
            //Created by: -
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return CreateParameter(parameterName, SqlDbType.NVarChar, size, direction, isNullable, 0, 0, value);
        }
        /// <summary>
        /// Creates and returns a sql Decimal parameter
        /// </summary>
        public static SqlCeParameter CreateDecimalParameter(string parameterName, ParameterDirection direction, bool isNullable, byte precision, byte scale, object value)
        {
            //Created by: -
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return CreateParameter(parameterName, SqlDbType.Decimal, 0, direction, isNullable, precision, scale, value);
        }

        public static bool AddAuditableColumns(string _ConnString)
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "ALTER TABLE tb_Vendor_Visit_Images ADD [ImageServerID] int,[PoleServerID] int,[StructureServerID] int,[UploadedDate] datetime";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                _Query = "ALTER TABLE tb_Vendor_Visit_XStructureMappingPoles ADD [PoleServerID] int, [StructureServerID] int, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                _Query = "ALTER TABLE tb_Vendor_Visits ADD [StructureServerID] int, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool AddAuditableInspectionColumns(string _ConnString)
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query = "ALTER TABLE tb_Images ADD [InspectionServerID] int,[InspectionResultServerID] int,[ImageServerID] int,[UploadedDate] datetime";
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                _Query = "ALTER TABLE tb_UtilityInspectionResults ADD [InspectionResultServerID] int, [InspectionServerID] int, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                _Query = "ALTER TABLE tb_UtilityInspections ADD [InspectionServerID] int, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool AddAuditableDistributionColumns(string _ConnString)
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;
            
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
               
                _Query = "ALTER TABLE tb_PoleImages ADD [PoleServerID] int, [ImageServerID] int, [UploadedDate] datetime";
                SqlCeCommand _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                _Query = "ALTER TABLE tb_Pole_InspectionResult ADD [PoleInspectionResultServerID] int, [PoleServerID] int, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                _Query = "ALTER TABLE tb_PhasesXPoles ADD [PhaseXPoleServerID] int, [PoleServerID] int, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                _Query = "ALTER TABLE tb_PoleResults ADD [PoleResultServerID] int, [PoleServerID] int, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                _Query = "ALTER TABLE tb_TopsXPoles ADD [TopXPoleServerID] int, [PoleServerID] int, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                _Query = "ALTER TABLE tb_Meters ADD [MeterServerID] int, [PoleServerID] int, UploadedImage bit, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                _Query = "ALTER TABLE tb_NotesXMeters ADD [NoteXMeterServerId] int, [MeterServerID] int, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                _Query = "ALTER TABLE tb_Sectionalizers ADD [SectionalizerServerID] int, [PoleServerID] int, UploadedImage bit, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                _Query = "ALTER TABLE tb_Reclosers ADD [RecloserServerID] int, [PoleServerID] int, UploadedImage bit, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                _Query = "ALTER TABLE tb_Transformers ADD [TransformerServerID] int, [PoleServerID] int, UploadedImage bit, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                _Query = "ALTER TABLE tb_Poles ADD [PoleServerID] int, [UploadedDate] datetime";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool UpdateDistributionVersion(string _connectionString)
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;

            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _connectionString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();


                //Check if necesary the update
                _Query = "SELECT SettingId FROM tb_Settings WHERE SettingKey = '12'";
                SqlCeCommand _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                SqlCeDataReader dr = _SqlCeCommand.ExecuteReader();
                bool hasSettingKey = dr.Read();

                //2nd validation to check if necesary the update
                _Query = "SELECT COUNT(*) AS Total FROM tb_Lookups INNER JOIN tb_Poles ON tb_Lookups.LookupId = tb_Poles.SpecieFk WHERE tb_Lookups.LookupTypeFk = 'Species'";
                _SqlCeCommand = new SqlCeCommand(_Query, _MobileConnection);
                dr = _SqlCeCommand.ExecuteReader();
                bool hasCorrectFks = true;

                if (dr.Read())
                {
                    int numberOfIncorrects = dr.GetInt32(dr.GetOrdinal("Total"));

                    if (numberOfIncorrects > 0)
                        hasCorrectFks = false;
                }

                //Add necessary fields if missing
                //CreatePoleFields(_MobileConnection);

                return !hasSettingKey || !hasCorrectFks;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (_MobileConnection != null)
                {
                    _MobileConnection.Close();
                    _MobileConnection.Dispose();
                }
            }
        }


        public static void UpdateDBVersion(string _connectionString)
        {
            SqlCeConnection _MobileConnection = null;
            string _Query;

            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _connectionString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();

                //Check if necesary the update

                if (UpdateDistributionVersion(_connectionString)) 
                {
                    //Updates Settings File
                    UpdateSettingDistributionTable(_MobileConnection);

                    //Creates Pole field
                    CreatePoleFields(_connectionString);

                    //Updates column Name
                    UpdateFieldName(_MobileConnection);

                    //Updates FKs
                    UpdateLookupsFk(_MobileConnection);
                }

            }
            catch 
            {
                throw;
            }
            finally
            {
                if (_MobileConnection != null) 
                {
                    _MobileConnection.Close();
                    _MobileConnection.Dispose();
                }
            }            
        }

        public static void CreatePoleFields(string _connectionString)
        {
            SqlCeConnection connection = null;
            string _Query;

            try
            {
                //instance connection to mobile Databases
                connection = new SqlCeConnection("Data Source = " + _connectionString + " ");

                ExecuteQueryOnDb("ALTER TABLE tb_Poles ADD [D804] nvarchar(100)", connection);
                ExecuteQueryOnDb("ALTER TABLE tb_Poles ADD VendorFK int", connection);
                ExecuteQueryOnDb("ALTER TABLE tb_Poles ADD ProjectFK int", connection);
                ExecuteQueryOnDb("ALTER TABLE tb_Poles ADD InspectorFK int", connection);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            } 
        }

        //EXECUTES A QUERY ON THE DATABASE. RETURNS TRUE IF WAS CORRECTLY EXECUTED.
        private static bool ExecuteQueryOnDb(string Query, SqlCeConnection connection) {
            try
            {
                SqlCeCommand _SqlCeCommand = new SqlCeCommand(Query, connection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;
            }
            catch {
                return false;
            }
        }

        private static void UpdateLookupsFk(SqlCeConnection connection)
        {
            string query = "SELECT PoleId, SpecieFk, ClassFk, LengthFk, TreatmentFk FROM tb_Poles ";
            SqlCeCommand _SqlCeCommand = new SqlCeCommand(query, connection);
            SqlCeDataReader dr = _SqlCeCommand.ExecuteReader();
            
            while (dr.Read())
            {
                int fkSpecieRealValue = Int32.MinValue;
                int fkClassRealValue = Int32.MinValue;
                int fkLengthRealValue = Int32.MinValue;
                int fkTreatmentRealValue = Int32.MinValue;
                int poleId = Convert.ToInt32(dr["PoleId"]);

                if (dr["SpecieFk"] != DBNull.Value ) 
                    fkSpecieRealValue = SearchValidLookUpId(connection, Convert.ToInt32(dr["SpecieFk"]));

                if (dr["ClassFk"] != DBNull.Value)
                    fkClassRealValue = SearchValidLookUpId(connection, Convert.ToInt32(dr["ClassFk"]));

                if (dr["LengthFk"] != DBNull.Value)
                    fkLengthRealValue = SearchValidLookUpId(connection, Convert.ToInt32(dr["LengthFk"]));

                if (dr["TreatmentFk"] != DBNull.Value)
                    fkTreatmentRealValue = SearchValidLookUpId(connection, Convert.ToInt32(dr["TreatmentFk"]));

                if (IsFkUpdateNecessary(connection, Convert.ToInt32(dr["SpecieFk"])))
                {
                    query = "UPDATE tb_Poles SET ";

                    if (fkSpecieRealValue != Int32.MinValue)
                        query += "SpecieFk = " + fkSpecieRealValue + ", ";
                    else
                        query += "SpecieFk = NULL, ";

                    if (fkClassRealValue != Int32.MinValue)
                        query += "ClassFk =" + fkClassRealValue + ", ";
                    else
                        query += "ClassFk = NULL, ";

                    if (fkLengthRealValue != Int32.MinValue)
                        query += "LengthFk =" + fkLengthRealValue + ", ";
                    else
                        query += "LengthFk = NULL, ";

                    if (fkTreatmentRealValue != Int32.MinValue)
                        query += "TreatmentFk = " + fkTreatmentRealValue;
                    else
                        query += "TreatmentFk = NULL ";

                    query += " WHERE PoleId = " + poleId;

                    SqlCeCommand updCommand = new SqlCeCommand(query, connection);
                    updCommand.ExecuteNonQuery();
                }
            }

            query = "SELECT NoteXMeterId, NoteFk FROM tb_NotesXMeters ";
            _SqlCeCommand = new SqlCeCommand(query, connection);
            dr = _SqlCeCommand.ExecuteReader();

            while (dr.Read())
            {
                int fkMeterNoteRealValue = Int32.MinValue;
                int meterNoteId = Convert.ToInt32(dr["NoteXMeterId"]);

                if (dr["NoteFk"] != DBNull.Value)
                    fkMeterNoteRealValue = SearchValidLookUpId(connection, Convert.ToInt32(dr["NoteFk"]));

                query = "UPDATE tb_NotesXMeters SET ";

                if (fkMeterNoteRealValue != Int32.MinValue)
                    query += "NoteFk = " + fkMeterNoteRealValue + " ";
                else
                    query += "NoteFk = NULL ";

                query += " WHERE NoteXMeterId = " + meterNoteId;

                SqlCeCommand updCommand = new SqlCeCommand(query, connection);
                updCommand.ExecuteNonQuery();
            }

            query = "SELECT PoleInspectionResultId, InspectionTypeFk, ResultTypeFk FROM tb_Pole_InspectionResult ";
            _SqlCeCommand = new SqlCeCommand(query, connection);
            dr = _SqlCeCommand.ExecuteReader();

            while (dr.Read())
            {
                int fkInspectionTypeRealValue = Int32.MinValue;
                int fkResultTypeRealValue = Int32.MinValue;
                int poleInspectionResultId = Convert.ToInt32(dr["PoleInspectionResultId"]);

                if (dr["InspectionTypeFk"] != DBNull.Value)
                    fkInspectionTypeRealValue = SearchValidLookUpId(connection, Convert.ToInt32(dr["InspectionTypeFk"]));

                if (dr["ResultTypeFk"] != DBNull.Value)
                    fkResultTypeRealValue = SearchValidLookUpId(connection, Convert.ToInt32(dr["ResultTypeFk"]));

                query = "UPDATE tb_Pole_InspectionResult SET ";

                if (fkInspectionTypeRealValue != Int32.MinValue)
                    query += "InspectionTypeFk = " + fkInspectionTypeRealValue + ", ";
                else
                    query += "InspectionTypeFk = NULL, ";

                if (fkResultTypeRealValue != Int32.MinValue)
                    query += "ResultTypeFk = " + fkResultTypeRealValue + " ";
                else
                    query += "ResultTypeFk = NULL ";

                query += " WHERE PoleInspectionResultId = " + poleInspectionResultId;

                SqlCeCommand updCommand = new SqlCeCommand(query, connection);
                updCommand.ExecuteNonQuery();
            }

            query = "SELECT TopXPoleId, TopFk FROM tb_TopsXPoles ";
            _SqlCeCommand = new SqlCeCommand(query, connection);
            dr = _SqlCeCommand.ExecuteReader();

            while (dr.Read())
            {
                int fkTopRealValue = Int32.MinValue;
                int topXPoleId = Convert.ToInt32(dr["TopXPoleId"]);

                if (dr["TopFk"] != DBNull.Value)
                    fkTopRealValue = SearchValidLookUpId(connection, Convert.ToInt32(dr["TopFk"]));


                query = "UPDATE tb_TopsXPoles SET ";

                if (fkTopRealValue != Int32.MinValue)
                    query += "TopFk = " + fkTopRealValue + " ";
                else
                    query += "TopFk = NULL ";

                query += " WHERE TopXPoleId = " + topXPoleId;

                SqlCeCommand updCommand = new SqlCeCommand(query, connection);
                updCommand.ExecuteNonQuery();
            }


            query = "SELECT PhaseXPoleId, PhaseFk FROM tb_PhasesXPoles ";
            _SqlCeCommand = new SqlCeCommand(query, connection);
            dr = _SqlCeCommand.ExecuteReader();

            while (dr.Read())
            {
                int fkPhaseRealValue = Int32.MinValue;
                int phaseXPoleId = Convert.ToInt32(dr["PhaseXPoleId"]);

                if (dr["PhaseFk"] != DBNull.Value)
                    fkPhaseRealValue = SearchValidLookUpId(connection, Convert.ToInt32(dr["PhaseFk"]));

                query = "UPDATE tb_PhasesXPoles SET ";

                if (fkPhaseRealValue != Int32.MinValue)
                    query += "PhaseFk = " + fkPhaseRealValue + " ";
                else
                    query += "PhaseFk = NULL ";

                query += " WHERE PhaseXPoleId = " + phaseXPoleId;

                SqlCeCommand updCommand = new SqlCeCommand(query, connection);
                updCommand.ExecuteNonQuery();
            }


            query = "SELECT PoleResult, ResultTypeFk FROM tb_PoleResults ";
            _SqlCeCommand = new SqlCeCommand(query, connection);
            dr = _SqlCeCommand.ExecuteReader();

            while (dr.Read())
            {
                int fkResultTypeRealValue = Int32.MinValue;
                int poleResultId = Convert.ToInt32(dr["PoleResult"]);

                if (dr["ResultTypeFk"] != DBNull.Value)
                    fkResultTypeRealValue = SearchValidLookUpId(connection, Convert.ToInt32(dr["ResultTypeFk"]));

                query = "UPDATE tb_PoleResults SET ";

                if (fkResultTypeRealValue != Int32.MinValue)
                    query += "ResultTypeFk = " + fkResultTypeRealValue + " ";
                else
                    query += "ResultTypeFk = NULL ";

                query += " WHERE PoleResult = " + poleResultId;

                SqlCeCommand updCommand = new SqlCeCommand(query, connection);
                updCommand.ExecuteNonQuery();
            }

        }

        private static int SearchValidLookUpId (SqlCeConnection connection, int lookUpId)
        {
            string query = "SELECT LookupValue FROM tb_Lookups WHERE LookUpId = " + lookUpId;
            SqlCeCommand _SqlCeCommand = new SqlCeCommand(query, connection);
            SqlCeDataReader dr = _SqlCeCommand.ExecuteReader();

            if (dr.Read())
                return Convert.ToInt32(dr["LookupValue"]);

            return Int32.MinValue;
        }

        private static bool IsFkUpdateNecessary(SqlCeConnection connection, int specieFk)
        {
            string query = "SELECT LookupValue FROM tb_Lookups WHERE LookUpTypeFk = 'Species' AND LookUpValue = " + specieFk;
            SqlCeCommand _SqlCeCommand = new SqlCeCommand(query, connection);
            SqlCeDataReader dr = _SqlCeCommand.ExecuteReader();

            if (dr.Read())
                return false;

            return true;
        }

        private static void UpdateFieldName(SqlCeConnection connection)
        {
            //Checks if column exists
            string query = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Lookups' AND COLUMN_NAME = 'LookupTypeFk'";
            SqlCeCommand _SqlCeCommand = new SqlCeCommand(query, connection);
            SqlCeDataReader dr = _SqlCeCommand.ExecuteReader();

            if (!dr.Read())
            {
                //If column LookupTypeFk doesnt exist we create it
                query = "ALTER TABLE tb_Lookups ADD [tmpColumn] nvarchar(20)";
                _SqlCeCommand = new SqlCeCommand(query, connection);
                _SqlCeCommand.ExecuteNonQuery();

                query = "UPDATE tb_Lookups SET tmpColumn = LookupType";
                _SqlCeCommand = new SqlCeCommand(query, connection);
                _SqlCeCommand.ExecuteNonQuery();

                query = "ALTER TABLE tb_Lookups DROP COLUMN LookupType";
                _SqlCeCommand = new SqlCeCommand(query, connection);
                _SqlCeCommand.ExecuteNonQuery();

                query = "ALTER TABLE tb_Lookups ADD [LookupTypeFk] nvarchar(20)";
                _SqlCeCommand = new SqlCeCommand(query, connection);
                _SqlCeCommand.ExecuteNonQuery();

                query = "UPDATE tb_Lookups SET LookupTypeFk = tmpColumn";
                _SqlCeCommand = new SqlCeCommand(query, connection);
                _SqlCeCommand.ExecuteNonQuery();

                query = "ALTER TABLE tb_Lookups DROP COLUMN tmpColumn";
                _SqlCeCommand = new SqlCeCommand(query, connection);
                _SqlCeCommand.ExecuteNonQuery();
            }
        }

        private static void UpdateSettingDistributionTable(SqlCeConnection connection)
        {
            string query = "SELECT SettingValue FROM tb_Settings WHERE SettingKey = '8'";
            SqlCeCommand _SqlCeCommand = new SqlCeCommand(query, connection);
            SqlCeDataReader dr = _SqlCeCommand.ExecuteReader();

            if (dr.Read())
            {
                string value = dr["SettingValue"].ToString();
                string newValue = value == "1" ? "DistributionInspection" : "DistributionGPS";

                query = "UPDATE tb_Settings SET SettingName = '" + newValue + "' WHERE SettingKey = '8'";
                _SqlCeCommand = new SqlCeCommand(query, connection);
                _SqlCeCommand.ExecuteNonQuery();

                query = "INSERT INTO tb_Settings (SettingKey, SettingName, SettingValue) VALUES ";
                query += "('12', 'DistributionAppMode', '" + value + "')";
                _SqlCeCommand = new SqlCeCommand(query, connection);
                _SqlCeCommand.ExecuteNonQuery();
            }
        }

        public static bool AddProjectColumn(string _ConnString)
        {
            SqlCeConnection _MobileConnection = null;
            StringBuilder  _Query = new StringBuilder();
            try
            {
                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                _Query.Append("ALTER TABLE tb_Vendor_Visits ADD [ProjectFK] int");
                SqlCeCommand _SqlCeCommand;
                _SqlCeCommand = new SqlCeCommand(_Query.ToString(), _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertProject(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Select count(*) from tb_Projects";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                if ((int)_SqlCeCommand.ExecuteScalar() == 0)
                {
                    string[] query = querys.Split("\r\n".ToCharArray());
                    foreach (string s in query)
                    {
                        if (!string.IsNullOrEmpty(s))
                        {
                            _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                            _SqlCeCommand.ExecuteNonQuery();
                        
                        }
                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertTopType(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Delete tb_Tops";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                string[] query = querys.Split("\r\n".ToCharArray());
                foreach (string s in query)
                {
                    if (!string.IsNullOrEmpty(s))
                    {
                        _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                        _SqlCeCommand.ExecuteNonQuery();

                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertVisitType(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Delete tb_Lookups where LookupTypeFk='VisitTypes'";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                string[] query = querys.Split("\r\n".ToCharArray());
                foreach (string s in query)
                {
                    if (!string.IsNullOrEmpty(s))
                    {
                        _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                        _SqlCeCommand.ExecuteNonQuery();

                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }
        public static bool InsertClasses(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Delete tb_Lookups where LookupTypeFk='Classes'";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                string[] query = querys.Split("\r\n".ToCharArray());
                foreach (string s in query)
                {
                    if (!string.IsNullOrEmpty(s))
                    {
                        _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                        _SqlCeCommand.ExecuteNonQuery();

                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertSpecies(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Delete tb_Lookups where LookupTypeFk='Species'";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                string[] query = querys.Split("\r\n".ToCharArray());
                foreach (string s in query)
                {
                    if (!string.IsNullOrEmpty(s))
                    {
                        _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                        _SqlCeCommand.ExecuteNonQuery();

                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertTreatments(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Delete tb_Lookups where LookupTypeFk='Treatments'";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                _SqlCeCommand.ExecuteNonQuery();

                string[] query = querys.Split("\r\n".ToCharArray());
                foreach (string s in query)
                {
                    if (!string.IsNullOrEmpty(s))
                    {
                        _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                        _SqlCeCommand.ExecuteNonQuery();

                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertContacts(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Select count(*) from tb_Users";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                if ((int)_SqlCeCommand.ExecuteScalar() == 0)
                {
                    string[] query = querys.Split("\r\n".ToCharArray());
                    foreach (string s in query)
                    {
                        if (!string.IsNullOrEmpty(s))
                        {
                            _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                            _SqlCeCommand.ExecuteNonQuery();

                        }
                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertUtilities(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Select count(*) from tb_Utility";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                if ((int)_SqlCeCommand.ExecuteScalar() == 0)
                {
                    string[] query = querys.Split("\r\n".ToCharArray());
                    foreach (string s in query)
                    {
                        if (!string.IsNullOrEmpty(s))
                        {
                            _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                            _SqlCeCommand.ExecuteNonQuery();

                        }
                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        public static bool InsertVendors(string _ConnString, string querys)
        {
            SqlCeConnection _MobileConnection = null;
            try
            {

                //instance connection to mobile Databases
                _MobileConnection = new SqlCeConnection("Data Source = " + _ConnString + " ");
                if (_MobileConnection.State == ConnectionState.Closed)
                    _MobileConnection.Open();
                SqlCeCommand _SqlCeCommand;
                string sql = "Select count(*) from tb_Vendor";
                _SqlCeCommand = new SqlCeCommand(sql, _MobileConnection);
                if ((int)_SqlCeCommand.ExecuteScalar() == 0)
                {
                    string[] query = querys.Split("\r\n".ToCharArray());
                    foreach (string s in query)
                    {
                        if (!string.IsNullOrEmpty(s))
                        {
                            _SqlCeCommand = new SqlCeCommand(s, _MobileConnection);
                            _SqlCeCommand.ExecuteNonQuery();

                        }
                    }
                }
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
            finally
            {
                _MobileConnection.Close();
                _MobileConnection.Dispose();
            }
        }

        /// <summary>
        /// Verifies if PROJECTS table was created. If not it will create it.
        /// </summary>
        /// <returns></returns>
        public static bool IsProjectTableCreated(string _ConnString)
        {
            SqlCeConnection _Con = null;
            SqlCeCommand _Com = null;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt;
            try
            {
                StringBuilder _Sql = new StringBuilder();
                _Con = GetConnection(_ConnString);

                #region Query
                _Sql.AppendLine(" SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME='tb_Projects'");
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql.ToString(), _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                if (_Dt.Rows.Count == 0)
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("Create Table tb_Projects");
                    _Sql.AppendLine("(");
                    _Sql.AppendLine("   ProjectId INT PRIMARY KEY,");
                    _Sql.AppendLine("   VendorFk INT ,");
                    _Sql.AppendLine("   Name nvarchar(100)");
                    _Sql.AppendLine(")");
                    _Com = new SqlCeCommand(_Sql.ToString(), _Con);
                    _Com.ExecuteNonQuery();
                }
                else
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("delete tb_Projects");
                    _Com = new SqlCeCommand(_Sql.ToString(), _Con);
                    _Com.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Verifies if UTILITIES table was created. If not it will create it
        /// </summary>
        /// <returns></returns>
        public static bool IsUtilityTableCreated(string _ConnString)
        {
            SqlCeConnection _Con = null;
            SqlCeCommand _Com = null;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt;
            try
            {
                StringBuilder _Sql = new StringBuilder();
                _Con = GetConnection(_ConnString);

                #region Query
                _Sql.AppendLine(" SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME='tb_Utility'");
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql.ToString(), _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                if (_Dt.Rows.Count == 0)
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("Create Table tb_Utility");
                    _Sql.AppendLine("(");
                    _Sql.AppendLine("   UtilityID INT PRIMARY KEY,");
                    _Sql.AppendLine("   Name nvarchar(100)");
                    _Sql.AppendLine(")");
                    _Com = new SqlCeCommand(_Sql.ToString(),_Con);
                    _Com.ExecuteNonQuery();
                }
                else
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("delete tb_Utility");
                    _Com = new SqlCeCommand(_Sql.ToString(), _Con);
                    _Com.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Verifies if USERS table was created. If not it will create it
        /// </summary>
        /// <returns></returns>
        public static bool IsUsersTableCreated(string _ConnString)
        {
            SqlCeConnection _Con = null;
            SqlCeCommand _Com = null;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt;
            try
            {
                StringBuilder _Sql = new StringBuilder();
                _Con = GetConnection(_ConnString);

                #region Query
                _Sql.AppendLine(" SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME='tb_Users'");
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql.ToString(), _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                if (_Dt.Rows.Count == 0)
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("Create Table tb_Users");
                    _Sql.AppendLine("(");
                    _Sql.AppendLine("   UserId INT PRIMARY KEY,");
                    _Sql.AppendLine("   VendorFk INT ,");
                    _Sql.AppendLine("   Name nvarchar(100)");
                    _Sql.AppendLine(")");
                    _Com = new SqlCeCommand(_Sql.ToString(),_Con);
                    _Com.ExecuteNonQuery();
                }
                else
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("delete tb_Users");
                    _Com = new SqlCeCommand(_Sql.ToString(), _Con);
                    _Com.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Verifies if Vendor table was created. If not it will create it
        /// </summary>
        /// <returns></returns>
        public static bool IsVendorTableCreated(string _ConnString)
        {
            SqlCeConnection _Con = null;
            SqlCeCommand _Com = null;
            SqlCeDataAdapter _Da = null;
            DataTable _Dt;
            try
            {
                StringBuilder _Sql = new StringBuilder();
                _Con = GetConnection(_ConnString);

                #region Query
                _Sql.AppendLine(" SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME='tb_Vendor'");
                #endregion Query
                _Da = new SqlCeDataAdapter(_Sql.ToString(), _Con);
                _Dt = new DataTable();
                _Dt.Clear();
                _Da.Fill(_Dt);
                if (_Dt.Rows.Count == 0)
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("Create Table tb_Vendor");
                    _Sql.AppendLine("(");
                    _Sql.AppendLine("   VendorId INT PRIMARY KEY,");
                    _Sql.AppendLine("   Name nvarchar(100)");
                    _Sql.AppendLine(")");
                    _Com = new SqlCeCommand(_Sql.ToString(), _Con);
                    _Com.ExecuteNonQuery();
                }
                else
                {
                    _Sql = new StringBuilder();
                    _Sql.AppendLine("delete tb_Vendor");
                    _Com = new SqlCeCommand(_Sql.ToString(), _Con);
                    _Com.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }


        #endregion DB
    }
}
